﻿using QWManage.Model;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace QWManage.DAL
{
    public class DepartmentDAL : SqlDAL
    {
        public DepartmentDAL()
        {
            TableName = "QW_Departments";
        }
        private void insert(DepartmentInfo obj)
        {
            string sql = $"insert into {TableName}(ID,Name,PID) values (@ID,@Name,@PID) ";
            using (SqlConnection conn = new SqlConnection(SQLCON))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@ID", obj.ID));
                cmd.Parameters.Add(new SqlParameter("@Name", obj.Name));
                cmd.Parameters.Add(new SqlParameter("@PID", obj.PID));
                cmd.ExecuteNonQuery();
            }
        }
        private void update(DepartmentInfo obj)
        {
            string sql = $"update {TableName} set Name=@Name,PID=@PID where ID=@ID";
            using (SqlConnection conn = new SqlConnection(SQLCON))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@Name", obj.Name));
                cmd.Parameters.Add(new SqlParameter("@PID", obj.PID));
                cmd.Parameters.Add(new SqlParameter("@ID", obj.ID));
                cmd.ExecuteNonQuery();
            }
        }

        public void Add(List<DepartmentInfo> departmentInfos)
        {
            foreach (DepartmentInfo department in departmentInfos)
            {
                if (GetDepartmentInfo(department.ID) == null)
                    insert(department);
                else
                    update(department);
            }
        }

        public DepartmentInfo GetDepartmentInfo(int id)
        {
            DepartmentInfo department = null;
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                string sql = $"select * from {TableName} where ID=" + id;
                SqlCommand cmd = new SqlCommand(sql, conn);
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        department = GetDepartmentInfo(reader);
                    }
                }
                return department;
            }
            finally
            {
                conn.Close();
            }
        }

        public List<DepartmentInfo> GetAll()
        {
            List<DepartmentInfo> list = new List<DepartmentInfo>();
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                string sql = $"select * from {TableName} order by [Name]";
                SqlCommand cmd = new SqlCommand(sql, conn);
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        DepartmentInfo departmentInfo = GetDepartmentInfo(reader);
                        list.Add(departmentInfo);
                    }
                }
                return list;
            }
            finally
            {
                conn.Close();
            }
        }

        public List<DepartmentInfo> GetSubDepartments(int id)
        {
            List<DepartmentInfo> list = new List<DepartmentInfo>();
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                string sql = $"select * from {TableName} where PID=" + id;
                SqlCommand cmd = new SqlCommand(sql, conn);
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        DepartmentInfo departmentInfo = GetDepartmentInfo(reader);
                        list.Add(departmentInfo);
                    }
                }
                return list;
            }
            finally
            {
                conn.Close();
            }
        }

        private DepartmentInfo GetDepartmentInfo(SqlDataReader reader)
        {
            DepartmentInfo departmentInfo = new DepartmentInfo();
            departmentInfo.ID = ReadInt(reader, "ID");
            departmentInfo.Name = ReadString(reader, "Name");
            departmentInfo.PID = ReadInt(reader, "PID");
            return departmentInfo;
        }
    }
}
